package mindteck.store;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CustomerDAO extends BaseDAO{

	private final String GETCUSTOMERBYIDQUERY = "SELECT * FROM CUSTOMERS WHERE CUSTOMERID = ?";
	private final String GETALLCUSTOMERSQUERY = "SELECT * FROM CUSTOMERS";
	private final String INSERTCUSTOMERQUERY = "INSERT INTO CUSTOMERS VALUES(?,?,?,?,?,?,?,?)";
	private final String GETCUSTOMERBYUSERID = "SELECT * FROM CUSTOMERS WHERE CUSTOMERS.USERID = ?";
	
	
	
	public CustomerDAO(){
		
	}
	
	
	public List<CustomerBean> getAllCustomers(){
		List<CustomerBean> customers = new ArrayList<CustomerBean>();
		Connection connect = null;
		
		try{
			connect = getPoolOfConnections();
			Statement statement = connect.createStatement();
			ResultSet rs = statement.executeQuery(GETALLCUSTOMERSQUERY);
			
			CustomerBean customerBean = null;
			while(rs.next()){
			    customerBean = new CustomerBean();
				
				customerBean.setFirstName(rs.getString("firstName"));
				customerBean.setLastName(rs.getString("lastName"));
				customerBean.setAddress(rs.getString("address"));
				customerBean.setCreditCardInfo(rs.getString("creditCardInfo"));
				customerBean.setCustomerId(rs.getInt("customerID"));
				customerBean.setPhoneNumber(rs.getString("phoneNumber"));
				customerBean.setShippingInfo(rs.getString("shippingInfo"));
				customerBean.setUserID(rs.getInt("userId"));
				
			}
			
			customers.add(customerBean);
			
		}catch(SQLException ex){
			
		}
		finally{
			if(connect != null)
				try {
					connect.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}				
			}
	
			return customers;
	}
	
	
	public boolean insertCustomer(CustomerBean customerBean){
		PreparedStatement pstatement = null;
		Connection connection = null;
		boolean t = true;
		
		try{
			connection = getPoolOfConnections();
			pstatement = connection.prepareStatement(INSERTCUSTOMERQUERY);
			
			/*setting up insert statement's values*/
			pstatement.setInt(1, customerBean.getCustomerId());
			pstatement.setString(2, customerBean.getFirstName());
			pstatement.setString(3, customerBean.getLastName());
			pstatement.setString(4, customerBean.getPhoneNumber());
			pstatement.setString(5, customerBean.getAddress());
			pstatement.setString(6, customerBean.getShippingInfo());
			pstatement.setString(7, customerBean.getCreditCardInfo());
			pstatement.setInt(8, customerBean.getUserID());
			
			pstatement.executeQuery();
			
			
		}catch(SQLException sqlex){
			t = false;
			sqlex.printStackTrace();
		}finally{
			if(connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return t;
	}
	
	public CustomerBean getCustomerByCustomerId(int id){
		CustomerBean customer = null;
		PreparedStatement pstatement = null;
		Connection connection = null;
		
		try{
			connection = getPoolOfConnections();
			pstatement = connection.prepareStatement(GETCUSTOMERBYIDQUERY);
			pstatement.setInt(1, id);
			ResultSet rs = pstatement.executeQuery();
			customer = new CustomerBean();
			
			if(rs.next()){
			customer.setFirstName(rs.getString("firstName"));
			customer.setLastName(rs.getString("lastName"));
			customer.setAddress(rs.getString("address"));
			customer.setPhoneNumber(rs.getString("phoneNumber"));
			customer.setCreditCardInfo(rs.getString("creditCardInfo"));
			customer.setCustomerId(id);
			customer.setShippingInfo(rs.getString("shippingInfo"));
			customer.setUserID(rs.getInt("userId"));
			}
			
			
		}catch(SQLException sqle){
			sqle.printStackTrace();
		}finally{
			if(connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		}
		
		return customer;
	}
	
	public CustomerBean getCustomerByUserId(int userId){
		CustomerBean customerBean = new CustomerBean();
		PreparedStatement pst = null;
		Connection connection = null;
		
		try {
			connection = getPoolOfConnections();
			pst = connection.prepareStatement(GETCUSTOMERBYUSERID);
			pst.setInt(1, userId);
			ResultSet rs = pst.executeQuery();
			
			if(rs.next()){
			customerBean.setAddress(rs.getString("address"));
			customerBean.setCreditCardInfo(rs.getString("creditCardInfo"));
			customerBean.setCustomerId(rs.getInt("customerId"));
			customerBean.setFirstName(rs.getString("firstName"));
			customerBean.setLastName(rs.getString("lastName"));
			customerBean.setPhoneNumber(rs.getString("phoneNumber"));
			customerBean.setShippingInfo(rs.getString("shippingInfo"));
			customerBean.setUserID(userId);
			}
			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			if(connection != null){
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
				
		return customerBean;
	}
	
	
	
	}



	

